package com.portal.dao;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import oracle.jdbc.OracleTypes;
import org.apache.log4j.Logger;


import com.portal.dto.AlertaDTO;
import com.portal.dto.PacienteDTO;
import utiles.AppProperties;
import utiles.ConexionPool;

public class PacienteFarmaciaDAO {
	private static Logger log = Logger.getLogger(PacienteFarmaciaDAO.class);
	String pkgVar = (AppProperties.getProperty("aplicacion", "PACKAGE"));

	public  List<PacienteDTO> getAllPaciente(String nombre) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    	
		List<PacienteDTO> listPaciente = new ArrayList<PacienteDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_PAC_PACIENTE.getallpacienteFrm(?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, nombre);
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);
			while (rs.next()) {
				PacienteDTO listpac = new PacienteDTO();
				listpac.setRut(rs.getString(1));
				listpac.setDv(rs.getString(2));
				listpac.setNombreCompleto(rs.getString(3));
				listPaciente.add(listpac);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error PacienteFarmaciaDAO.getAllPaciente SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listPaciente;

	}

	public  List<PacienteDTO> getPacienteByRut(String rutPac) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    	
		List<PacienteDTO> listPaciente = new ArrayList<PacienteDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_PAC_PACIENTE.getPacienteFrm(?,?) }";
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, rutPac);
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);
			while (rs.next()) {
				PacienteDTO listpac = new PacienteDTO();
				listpac.setRut(rs.getString(1));
				listpac.setDv(rs.getString(2));
				listpac.setNombreCompleto(rs.getString(3));
				listpac.setApePaterno(rs.getString(4));
				listpac.setApeMaterno(rs.getString(5));
				listpac.setNombre(rs.getString(6));
				listpac.setFecNac(rs.getString(7));
				listpac.setSexo(rs.getString(8));
				listpac.setDireccion(rs.getString(9));
				listpac.setComuna(rs.getString(10));
				listpac.setRegion(rs.getString(11));
				listpac.setIdComuna(rs.getString(12));
				listpac.setIdRegion(rs.getString(13));
				listpac.setIdPais(rs.getString(14));
				listpac.setFichaSocial(rs.getString(15));
				listpac.setFecIngreso(rs.getString(16));
				listpac.setIdDiagnostico(rs.getString(17));
				listpac.setTelefonos(rs.getString(18));
				listpac.setFonoMovil(rs.getString(19));
				listpac.setEmail(rs.getString(21));
				listpac.setPrograma(rs.getString(22));
				listpac.setIdUsuario(rs.getString(23));
				listpac.setSede(rs.getString(24));
				listpac.setObservaciones(rs.getString(25));
				listpac.setIdMedico(rs.getString(26));
				listpac.setRutMedico(rs.getString(27));
				listpac.setMedico(rs.getString(28));

				listPaciente.add(listpac);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error PacienteFarmaciaDAO.getPacienteByRut SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listPaciente;
	}
	
	
	public  List<AlertaDTO> getAlertasByRut(String rutPac, String tipo) throws SQLException, IOException{
		//System.out.println("entro en getAlertaByRut"+rutPac);
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    		
		List<AlertaDTO> listAlerta = new ArrayList<AlertaDTO>();
		try {
			String query = "{ call "+pkgVar+".PKG_PAC_PACIENTE.getAlertaFrm(?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, rutPac);
			stmt.setString(2, tipo);
			stmt.registerOutParameter(3, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(3);
			while (rs.next()) {
				AlertaDTO alertaDTO = new AlertaDTO();
				alertaDTO.setUsuario(rs.getString(1));
				//System.out.println(rs.getString(1)+";"+rs.getString(2)+";"+rs.getString(3));
				alertaDTO.setFecDigitacion(rs.getString(2));
				alertaDTO.setMensaje(rs.getString(3));
				listAlerta.add(alertaDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error PacienteFarmaciaDAO.getAlertaByRut SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listAlerta;
	}
	  	
		public  List<PacienteDTO> getRutFicticio() throws SQLException, IOException{
			//ConexionPool connDao = new ConexionPool();
			Connection conn = ConexionPool.getConnectionPool();	
	    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
	    	List<PacienteDTO> listPaciente = new ArrayList<PacienteDTO>();
			try {
				//System.out.println("se envia rut:"+rut+" idPac:"+idPac+" idUser:"+idUser);
				String query = "{ call "+pkgVar+".Pkg_pac_paciente.getPacRutFicticio(?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.registerOutParameter(1, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(1);			
				while (rs.next()) {
					PacienteDTO listpac = new PacienteDTO();
					listpac.setRut(rs.getString(1));
					listpac.setDv(rs.getString(2));
					listPaciente.add(listpac);
				}			
				rs.close();
				stmt.close();
			} catch (SQLException ex) {  
				log.error("Error PacienteFarmaciaDAO.getPacRutFicticio SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();			
	            /*if (conn.isClosed()){
					System.out.println("conexion cerrada en getPaciente");
				}else{
					System.out.println("conexion NO cerrada en getPaciente");
				}*/
	            conn = null;
			}
			return listPaciente;	
		}	
		
		
		public  List<PacienteDTO> getDocumentosByPac(String rut) throws SQLException, IOException{
			//ConexionPool connDao = new ConexionPool();
			Connection conn = ConexionPool.getConnectionPool();	
	    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
	    	List<PacienteDTO> listPaciente = new ArrayList<PacienteDTO>();
			try {
				//System.out.println("se envia rut:"+rut+" idPac:"+idPac+" idUser:"+idUser);
				String query = "{ call "+pkgVar+".Pkg_pac_paciente.getDocumentosByPac(?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, rut);
				stmt.registerOutParameter(2, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(2);			
				while (rs.next()) {
					PacienteDTO listpac = new PacienteDTO();
					listpac.setObservaciones(rs.getString(2));
					listpac.setCantidad(rs.getString(3));
					listPaciente.add(listpac);
				}			
				rs.close();
				stmt.close();
			} catch (SQLException ex) {  
				log.error("Error PacienteFarmaciaDAO.getDocumentosByPac SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();			
	            /*if (conn.isClosed()){
					System.out.println("conexion cerrada en getPaciente");
				}else{
					System.out.println("conexion NO cerrada en getPaciente");
				}*/
	            conn = null;
			}
			return listPaciente;	
		}	
		
		
		
		public  int setActualizaPac(String rutf, String rutr, String idUsuario ) throws SQLException, IOException{
			//ConexionPool connDao = new ConexionPool();
			Connection conn = ConexionPool.getConnectionPool();	
	    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
	    	int retorno=0;	    	
			try {
				//System.out.println("se envia rut:"+rut+" idPac:"+idPac+" idUser:"+idUser);
				String query = "{ call "+pkgVar+".Pkg_pac_paciente.setActualizaPac(?,?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, rutf);
				stmt.setString(2, rutr);
				stmt.setString(3, idUsuario);
				//stmt.registerOutParameter(4, OracleTypes.CURSOR);
				stmt.execute();
				//ResultSet rs = (ResultSet)stmt.getObject(4);			
				/*while (rs.next()) {
					PacienteDTO listpac = new PacienteDTO();
					listpac.setObservaciones(rs.getString(2));
					listpac.setCantidad(rs.getString(3));
					listPaciente.add(listpac);
				}*/			
				//rs.close();
				stmt.close();
				retorno=1;
			} catch (SQLException ex) {  
				log.error("Error PacienteFarmaciaDAO.setActualizaPac SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();			
	            /*if (conn.isClosed()){
					System.out.println("conexion cerrada en getPaciente");
				}else{
					System.out.println("conexion NO cerrada en getPaciente");
				}*/
	            conn = null;
			}
			return retorno;	
		}	
		
		
		public  int setActualizaPacByDoc(String rutf,String rutr, String vfolio ) throws SQLException, IOException{
			Connection conn = ConexionPool.getConnectionPool();	
	    	int retorno=0;	    	
			try {
				//System.out.println("se envia rut:"+rut+" idPac:"+idPac+" idUser:"+idUser);
				String query = "{ call "+pkgVar+".Pkg_pac_paciente.setActualizaPacByDoc(?,?,?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, rutf);
				stmt.setString(2, rutr);
				stmt.setString(3, vfolio);
				stmt.execute();
				//System.out.println("rutf: "+rutf+"/rutr: "+rutr+"/folio: "+vfolio);
				//System.out.println(stmt.execute());
				stmt.close();
				retorno=1;
			} catch (SQLException ex) {  
				log.error("Error PacienteFarmaciaDAO.setActualizaPacByDoc SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				conn.close();			
	            conn = null;
			}
			
			return retorno;	
		}
		
		public  List<PacienteDTO> getPacienteRetira(String rutPac) throws SQLException, IOException{
			//ConexionPool connDao = new ConexionPool();
			Connection conn = ConexionPool.getConnectionPool();	
	    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
	    	
			List<PacienteDTO> listPaciente = new ArrayList<PacienteDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PAC_PACIENTE.getPacienteRetira(?,?) }";
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, rutPac);
				stmt.registerOutParameter(2, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(2);
				while (rs.next()) {
					PacienteDTO listpac = new PacienteDTO();
					listpac.setNombreCompleto(rs.getString(1));
					listPaciente.add(listpac);
				}
				rs.close();
				stmt.close();
			} catch (SQLException ex) {  
				log.error("Error PacienteFarmaciaDAO.getPacienteByRut SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
			return listPaciente;
		}
		
		
		public  List<PacienteDTO> getAllPacienteRetira(String rutPac) throws SQLException, IOException{
			//ConexionPool connDao = new ConexionPool();
			Connection conn = ConexionPool.getConnectionPool();	
	    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
	    	
			List<PacienteDTO> listPaciente = new ArrayList<PacienteDTO>();
			try {
				String query = "{ call "+pkgVar+".PKG_PAC_PACIENTE.getAllPacienteRetira(?,?) }";
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, rutPac);
				stmt.registerOutParameter(2, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(2);
				while (rs.next()) {
					PacienteDTO listpac = new PacienteDTO();
					listpac.setRut(rs.getString(1));
					listpac.setDv(rs.getString(2));
					listpac.setNombreCompleto(rs.getString(3));
					listPaciente.add(listpac);
				}
				rs.close();
				stmt.close();
			} catch (SQLException ex) {  
				log.error("Error PKG_PAC_PACIENTE.getAllPacienteRetira SQLException: " +ex.getMessage());
				System.out.println(ex.getMessage());
			} finally {
				//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
			}
			return listPaciente;
		}
		
		
		
		
		
}
